﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;

namespace StorageManage
{
    public class SqlHelper
    {
        static string connStr = ConfigurationManager.ConnectionStrings["StorageManageConnectionSql"].ConnectionString;
        //获取数据库表
        #region
        public static int ExecuteNonQuery(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        public static object ExecuteScalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandTimeout = 3600000;
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }
        public static DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();
            using (SqlDataAdapter sda = new SqlDataAdapter(sql, connStr))
            {
                sda.Fill(ds);
            }
            return ds;
        }
        public static SqlDataReader ExecuteReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    conn.Open();
                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                }
            }
            catch (Exception ex)
            {
                conn.Dispose();
                throw ex;
            }
        }
        public static DataTable GetDataTable(string sql)
        {
            DataSet ds = new DataSet();
            DataTable dt;
            using (SqlDataAdapter sda = new SqlDataAdapter(sql, connStr))
            {
                sda.Fill(ds);
                dt = ds.Tables[0];
            }
            return dt;
        }
        # endregion
        //对数据库执行多条SQL语句
        public static void ExecuteTrans(List<string> sqlTexts)
        {
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            SqlTransaction sqlTran = conn.BeginTransaction();
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = conn;
            sqlCmd.Transaction = sqlTran;
            sqlCmd.CommandTimeout = 0;
            try
            {
                foreach (string sqlText in sqlTexts)
                {
                    sqlCmd.CommandText = sqlText;
                    sqlCmd.ExecuteNonQuery();
                }
                sqlTran.Commit();
            }
            catch (Exception)
            {
                sqlTran.Rollback();
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public static DataTable GetPageList(PagerQueryParam pager)
        {
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(connStr))
            {
                using (SqlCommand sqlCmd = new SqlCommand("procpagwithprikey", con))
                {
                    sqlCmd.CommandType = CommandType.StoredProcedure;//设置调用的类型为存储过程  
                    AddParameterToStoredProc(sqlCmd, "@Table_Name", pager.TableName, SqlDbType.VarChar, 4000);
                    AddParameterToStoredProc(sqlCmd, "@Primary_Key", pager.PrimaryKey, SqlDbType.VarChar, 1000);
                    AddParameterToStoredProc(sqlCmd, "@Show_Fields", pager.ShowFields, SqlDbType.VarChar, 8000);
                    AddParameterToStoredProc(sqlCmd, "@Order_Field", pager.OrderField, SqlDbType.VarChar, 1000);
                    AddParameterToStoredProc(sqlCmd, "@OrderType", pager.OrderType.ToString(), SqlDbType.VarChar, 1000);
                    AddParameterToStoredProc(sqlCmd, "@where_Condition", pager.StrWhere, SqlDbType.VarChar, 4000);
                    AddParameterToStoredProc(sqlCmd, "@Page_Size", pager.PageSize.ToString(), SqlDbType.Int, 200);
                    AddParameterToStoredProc(sqlCmd, "@Page_Index", pager.PageIndex.ToString(), SqlDbType.Int, 200);

                    SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    da.Dispose();
                    dt = ds.Tables[0];
                }
            }
            return dt;
        }


        private static void AddParameterToStoredProc(SqlCommand cmd, string paramName, string paramValue,
         SqlDbType paramType, int paramSize)
        {
            SqlParameter sqlParam = new SqlParameter();

            sqlParam.ParameterName = paramName;
            sqlParam.SqlDbType = paramType;
            sqlParam.Value = paramValue;

            if (paramType == SqlDbType.VarChar || paramType == SqlDbType.Char)
                sqlParam.Size = paramSize;

            cmd.Parameters.Add(sqlParam);
            cmd.CommandTimeout = 0;

            return;
        }


    }

}
